package cn.benma666.kettle.yxjk;

import cn.benma666.crypt.DesUtil;
import cn.benma666.iframe.Result;
import cn.benma666.myutils.DateUtil;
import cn.benma666.myutils.StringUtil;
import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import org.pentaho.di.trans.TransMeta;

import java.io.IOException;
import java.sql.*;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

public class Sjztjk extends YxjkRunBase{
	private final static String QUERYSJK_BYID_SQL = "根据id查询数据载体配置信息sql";
	private final static String QUERYSJKBKJ_SQL = "监测表空间空闲率sql";
	private final static String PARAM_FREESIZE = "空闲表空间大小";
	private final static String PARAM_FREEPERCENT = "空闲表空间百分比";
	private final static String PARAM_SFJCBKJ = "是否监测表空间";
	private final static String PARAM_DECRYPT = "数据库连接密码解密密钥";
	private final static String PARAM_LOGINTIMEOUT = "连接超时（毫秒）";
	/**
	 * 表空间异常缓存<具体任务id，上次监测时间>：用于保存表空间异常的具体任务id，防止表空间重复监测。一天监测一次
	 */
	public static Map<String,String> tablespaceMap = new HashMap<String,String>();

    @Override
	public JkResult test(Object[] outputRow) {
    	String jtrwid = outputRow[getFieldIndex(JKRW_JTRW)].toString();//具体任务id
    	String code = outputRow[getFieldIndex(JKRW_RWDJ)].toString();//任务等级，对应消息级别
    	//数据载体信息对象
    	JSONObject sjztObj = null;
    	try {
    		//数据载体信息对象
        	sjztObj = YxjkInit.jkdb.findFirst(configInfo.getString(QUERYSJK_BYID_SQL), jtrwid);
		} catch (Exception e) {
			ku.logBasic("数据载体监控任务中的具体任务未找到："+jtrwid, e);
			return JkResult.error("数据载体监控任务中的具体任务未找到："+jtrwid,code,JkConstants.YCLX_007);
		}
    	String lx = sjztObj.getString("lx");
    	switch (lx) {
		case "oracle":
			return testSjk(outputRow,sjztObj,true);
		case "greenplum":
			return testSjk(outputRow,sjztObj,false);
		case "ftp":
			return testFtp(outputRow, sjztObj);
		default:
			break;
		}
    	return JkResult.success();
	}

    /**
     * 数据库监控
     * @param outputRow
     * @param sjztObj
     * @param flag 是否监测表空间
     * @return
     */
    private JkResult testSjk(Object[] outputRow,JSONObject sjztObj,boolean flag){
    	String jtrwid = outputRow[getFieldIndex(JKRW_JTRW)].toString();//具体任务id
    	String code = outputRow[getFieldIndex(JKRW_RWDJ)].toString();//任务等级，对应消息级别

    	String url = sjztObj.getString("ljc");
    	Connection connection = null;
        try {
        	Class.forName(JdbcUtils.getDriverClassName(url));
        } catch (Exception e) {
        	ku.logBasic("监测到数据库："+sjztObj.getString("mc")+"数据库连接测试：加载驱动失败！",e);
        	return JkResult.error("监测到数据库加载驱动失败：" + e.getMessage(),code,JkConstants.YCLX_012);
        }
		try{
			String user = sjztObj.getString("yhm");
	    	String password = DesUtil.decrypt(sjztObj.getString("mm"), configInfo.getString(PARAM_DECRYPT));
			DriverManager.setLoginTimeout(30);//表示30秒
			//mysql数据库可以此处url拼接，是因为DriverManager.setLoginTimeout(300)没有生效
//			connection = DriverManager.getConnection(url,user,password);

			//此处因为DriverManager.setLoginTimeout(300)没有生效，为解决连接oracle数据库超时问题，使用以下连接方式
			java.util.Properties info = new java.util.Properties();
	        if (user != null) {
	            info.put("user", user);
	        }
	        if (password != null) {
	            info.put("password", password);
	        }
	        info.put("oracle.jdbc.ReadTimeout", configInfo.getString(PARAM_LOGINTIMEOUT)); //ms  10秒
			//此处url拼接原因，是因为DriverManager.setLoginTimeout(300)没有生效
			connection = DriverManager.getConnection(url,info);

			//监测的数据库
	    	if(flag){
	    		String now = DateUtil.doFormatDate(new Date(), DateUtil.DATE_FORMATTER8);
				//不在map中 || map中的上次执行时间和当前时间不一致，进行表空间监测
	        	if(!tablespaceMap.containsKey(jtrwid)||!now.equals(tablespaceMap.get(jtrwid))){
	        		ku.logBasic("-----------开始监测"+outputRow[getFieldIndex(JKRW_RWMC)].toString()+"的表空间");
	        		Result result = testTablespace(connection,sjztObj);
	        		tablespaceMap.put(jtrwid, now);
					ku.logBasic("-----------数据库表空间监测结果：" + result.getMsg());
	        		//表空间监控产生的消息
	        		if(StringUtil.isNotBlank(result.getMsg())){
	        			return JkResult.success(result.getMsg(),result.getCode()+"");
	        		}
	        	}
	    	}
		}catch(Exception e){
			ku.logBasic("监测到数据库："+sjztObj.getString("mc")+"数据库连接测试：数据库连接失败！",e);
			return JkResult.error("监测到数据库异常：" + e.getMessage(),code,JkConstants.YCLX_013);
		}finally {
			try {
			    if (connection != null) {
			     connection.close();
			    }
			} catch (SQLException e) {
				return JkResult.error("数据库连接测试，数据库连接关闭失败：" + e.getMessage(),code,JkConstants.YCLX_014);
			}
		}
    	return JkResult.success();
    }


    /**
     * ftp监控
     * @param outputRow
     * @param sjztObj
     * @return
     */
    private JkResult testFtp(Object[] outputRow,JSONObject sjztObj){
    	String code = outputRow[getFieldIndex(JKRW_RWDJ)].toString();//任务等级，对应消息级别
    	FTPClient ftpClient = new FTPClient();
        ftpClient.setControlEncoding("utf-8");
        try {
        	String hostname = sjztObj.getString("ljc").split(":")[0];
        	int port = Integer.valueOf(sjztObj.getString("ljc").split(":")[1]);
        	String username = sjztObj.getString("yhm");
        	String password = DesUtil.decrypt(sjztObj.getString("mm"), configInfo.getString(PARAM_DECRYPT));

            ftpClient.connect(hostname, port); //连接ftp服务器
            ftpClient.login(username, password);//登录ftp服务器
            int reply=ftpClient.getReplyCode(); //是否成功登录服务器
            if(!FTPReply.isPositiveCompletion(reply)){
            	ftpClient.disconnect();
            	JkResult.error("FTP "+sjztObj.getString("ljc")+" refused connectd",code,JkConstants.YCLX_015);
            }
        } catch (Exception e) {
            e.printStackTrace();
            JkResult.error("FTP "+sjztObj.getString("ljc")+" refused connectd",code,JkConstants.YCLX_015);
        } finally {
        	try {
                if (ftpClient != null && ftpClient.isConnected()) {
                    ftpClient.abort();
                    ftpClient.disconnect();
                }
            } catch (IOException e) {
                e.printStackTrace();
                JkResult.error("FTP "+sjztObj.getString("ljc")+" close failed",code,JkConstants.YCLX_015);
            }
		}
		return JkResult.success();
    }


    /**
     * 表空间监测:空闲率不足2%且不足20G的表空间
     * @return
     */
    public Result testTablespace(Connection conn,JSONObject sjztObj){
    	//消息级别严重
    	String code = "2";
    	//空闲率不足2%的表空间，消息级别严重
    	String msg = "";
    	//空闲表空间大小
    	double freeSize = configInfo.getDouble(PARAM_FREESIZE);
    	//空闲表空间百分比
    	double freePercent = configInfo.getDouble(PARAM_FREEPERCENT);
    	//是否监测表空间
    	String sfjcbkj = configInfo.getString(PARAM_SFJCBKJ).toUpperCase();
    	JSONObject kzxx = null;
		if(!"{}".equals(sjztObj.getString("kzxx"))){
    		try {
        		//当前数据库监测表空间的配置信息
    			kzxx = JSONObject.parseObject(sjztObj.getString("kzxx")).getJSONObject("表空间监控");
            	if(StringUtil.isBlank(kzxx.getString(PARAM_FREESIZE))
            			|| StringUtil.isBlank(kzxx.getString(PARAM_FREEPERCENT))
            				||StringUtil.isBlank(kzxx.getString(PARAM_SFJCBKJ))){
            		return Result.success("数据库："+sjztObj.getString("dm")+"中扩展信息配置有误！",code);
            	}else{
            		freeSize = kzxx.getDoubleValue(PARAM_FREESIZE);
            		freePercent = kzxx.getDoubleValue(PARAM_FREEPERCENT);
            		sfjcbkj = kzxx.getString(PARAM_SFJCBKJ).toUpperCase();
            	}
    		} catch (Exception e) {
    			ku.logBasic("数据库："+sjztObj.getString("dm")+"中扩展信息配置有误:"+sjztObj.getString("kzxx"), e);
    			return Result.success("数据库："+sjztObj.getString("dm")+"中扩展信息配置有误！",code);
    		}
    	}
		if(!"Y".equals(sfjcbkj)){
			return new Result(true);
		}
		try {
	    	PreparedStatement ps = conn.prepareStatement(configInfo.getString(QUERYSJKBKJ_SQL));
	    	ResultSet rs = ps.executeQuery();
	    	while(rs.next()) {
	    		String mc = rs.getString("表空间名");
				//针对此表空间的配置信息不为空
				if(kzxx!=null&&kzxx.getJSONObject(mc)!=null){
					JSONObject pzxxobj = kzxx.getJSONObject(mc);
					freeSize = pzxxobj.getDoubleValue(PARAM_FREESIZE);
	        		freePercent = pzxxobj.getDoubleValue(PARAM_FREEPERCENT);
	        		sfjcbkj = pzxxobj.getString(PARAM_SFJCBKJ).toUpperCase();
				}
				if(!"Y".equals(sfjcbkj)){
					continue;
				}
				//空闲表空间比小于2%，表空间小于20G 推送消息
				if(Double.valueOf(rs.getString(PARAM_FREESIZE)) <= (freeSize*1024)
						&&Double.valueOf(rs.getString(PARAM_FREEPERCENT)) <= freePercent){
					msg = msg + rs.getString("表空间名") + "、";
				}
	    	}
		}catch (Exception e) {
			return Result.success("数据库："+sjztObj.getString("dm")+"查询表空间出错！",code);
    	}
		if(StringUtil.isNotBlank(msg)){
			msg = msg.substring(0, msg.length()-1);
			//不影响监控状态，仅仅发送消息
			return Result.success("空闲表空间百分比不足"+freePercent+"%且空闲表空间大小不足"+freeSize+"G:"+msg,code);
		}else{
			//表空间监测正常
		}
		return new Result(true);
    }

    @Override
	public String getDefaultConfigInfo(TransMeta transMeta, String stepName) throws Exception {
		//创建一个JSON对象，用于构建配置对象，避免直接拼字符串构建JSON字符串
        JSONObject params = new JSONObject();
        params.put(QUERYSJK_BYID_SQL, "select * from SYS_SJGL_SJZT where id = ?");
        params.put(QUERYSJKBKJ_SQL, "SELECT UPPER(F.TABLESPACE_NAME) \"表空间名\",TO_CHAR(ROUND((F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100，2),'999.99')\"使用比\",D.TOT_GROOTTE_MB-F.TOTAL_BYTES \"空闲表空间大小\",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'999.99') \"空闲表空间百分比\" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024 * 1024),2) MAX_BYTES FROM SYS.DBA_SEGMENTS GROUP BY TABLESPACE_NAME)F,(SELECT DD.TABLESPACE_NAME,ROUND(SUM(CASE WHEN AUTOEXTENSIBLE='YES' THEN DD.MAXBYTES ELSE DD.BYTES END)/(1024*1024),2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME)D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC ");
        params.put(PARAM_FREESIZE, "20");
        params.put(PARAM_FREEPERCENT, "2");
        params.put(PARAM_SFJCBKJ, "Y");
        params.put(PARAM_DECRYPT, "5zIcmw5qVZs=");
        params.put(PARAM_LOGINTIMEOUT, "10000");
		return JSON.toJSONString(params, true);
	}

}
